package DB_Layer;

import java.sql.*;
import java.util.ArrayList;

import Model_Layer.*;

/***
 * DB_customer.java
 * UCN dmaa0214 - October 2014 
 * 
 * @author Group 5 
 * @version 1.0
 * 
 */
public class DB_Supplier implements IFDB_Supplier{
	private Connection con;
		
	public DB_Supplier() {
		con = DbConnection.getInstance().getDBcon();
	}
	
	/*
	 *  public methods
	 */

	public int insertSupplier(Supplier sup) throws Exception {
		
		// finding highest value of supplier ID
		int nextSupID = GetMaxID.getMaxID("SELECT max(supID) from Supplier");
		nextSupID = nextSupID + 1;
		System.out.println("Next supplier ID = " + nextSupID);
		
		int rc = -1;
		String query = "INSERT INTO Supplier(SupID, Name, Address, Phoneno, Email, Country, ZipCode) " + 
		   " VALUES('" + 
		
		// suppliers fields
		nextSupID + "','" +
		sup.getName() + "','" + 
		sup.getAddress() + "','" +
		sup.getPhoneNo() + "','" +
		sup.getEmail() + "','" + 
		sup.getCountry() + "'," + 		
		sup.getZipCode() + ");";
		 				
		System.out.println("Insert : " + query);
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			rc = statement.executeUpdate(query);
			statement.close();
		}
		catch(Exception e) {
			System.out.println("Supplier was not created! ");
			throw new Exception ("Supplier was not inserted corrrectly! ");
		}
		
		return(rc);
	}
	
	public int updateSupplier(Supplier sup) {
		
		Supplier supObj = sup;
		int rc = -1;
		
		String query = "UPDATE Supplier SET " + 
				"SupID = '" + supObj.getSupID() + "', " + 
				"Name = '" + supObj.getName() + "', " + 
				"Address = '" + supObj.getAddress() + "', " + 
				"PhoneNo = '" + supObj.getPhoneNo() + "', " + 
				"Email = '" + supObj.getEmail() + "', " +
				"Country = '" + supObj.getCountry() + "'," + 
				"ZipCode = " + supObj.getZipCode() + ";";		
		
		System.out.println("Update query: " + query);
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			rc = statement.executeUpdate(query);
			statement.close();
		}
		catch(Exception e) {
			System.out.println("Update exception in supplier db: " + e);
		}
		
		return(rc);
	}
	
	// gets one customer using name 
	public Supplier getSupplier(int supID) {
		
		String wClause = "supID = '" + supID + "'"; 
		return singleWhere(wClause, true);
	}
	
	public int deleteSupplier(int supID) {
		
		int rc = -1;
		
		String query = "DELETE FROM Supplier WHERE supID = '" + supID + "'";
		System.out.println(query);		
				
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			rc = statement.executeUpdate(query);
			statement.close();
		}
		catch(Exception e) {
			System.out.println("Delete exception in supplier db: " + e);
		}
		
		return(rc);
	}
	
	
	public ArrayList<Supplier> getAllSuppliers() {
		return miscWhere("", false);
	}
		
	
	/*
	 * private methods
	 */
	
    //michWere is used whenever we want to select more than one customer
	private ArrayList<Supplier> miscWhere(String wClause, boolean retrieveAssociation ) {
		
		ResultSet results;
		ArrayList<Supplier> list = new ArrayList<Supplier>();
		
		String query = buildQuery(wClause);
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			results = statement.executeQuery(query);
		
		
			while(results.next()) {
				Supplier supObj = new Supplier();
				supObj = buildSupplier(results);
				list.add(supObj);
			}
			
			statement.close();	
			
			
			//if(retrieveAssociation) {
				for(Supplier supObj : list) {
					int findZipCode = supObj.getZipCode();
					String city = getCityFromZipCode(findZipCode);
					supObj.setCity(city);
				}
			//}
			
			
		}
		catch(Exception e) {
			System.out.println("Query Exception - Select: " + e);
			e.printStackTrace();
		}
		
		return list;
	}
	

	
	//Singelwhere is used when we only select one customer
	private Supplier singleWhere(String wClause, boolean retriveAssociation) {
		
		ResultSet results;
		Supplier supObj = new Supplier();
		
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			results = statement.executeQuery(query);
			
			if(results.next()) {
				
				supObj = buildSupplier(results);
				statement.close();		
				
				//if(retriveAssociation) {
					int zipcode = supObj.getZipCode();
					String city = getCityFromZipCode(zipcode);
					supObj.setCity(city);
				//}
			}
			else {
				supObj = null;
			}
		}
		catch(Exception e) {
			System.out.println("Query Exception: " + e);
		}
		
		return supObj;
	}
	
	// method to build the query
	private String buildQuery(String wClause) {
		
		String query = "SELECT Name, Address, PhoneNo, Email, Private_Business, "
				+ "Discount, Country, ZipCode from Customer";
		
		if (wClause.length() > 0) 
			query = query + " WHERE " + wClause;
		
		return query;
	}

	// method to build a supplier object
	private Supplier buildSupplier(ResultSet results) {
		
		Supplier supObj = new Supplier();
		
		// Columns for table supplier are used
		try {
			supObj.setSupID(results.getInt("SupID"));
			supObj.setName(results.getString("Name"));
			supObj.setAddress(results.getString("Address"));
			supObj.setPhoneNo(results.getString("PhoneNo"));
			supObj.setEmail(results.getString("Email"));
			supObj.setCountry(results.getString("Country"));
			supObj.setZipCode(results.getInt("ZipCode"));
		}
		catch(Exception e) {
			System.out.println("Error in building the supplier object: " + e);
		}
				
		return supObj;
	}
	
	private String getCityFromZipCode(int zipcode) {
		String query = "SELECT City FROM Zipcode_City WHERE Zipcode = '" + zipcode + "';";
		
		String foundCity = null;
		
		try {
			Statement statement = con.createStatement();
			statement.setQueryTimeout(5);
			ResultSet cityresult = statement.executeQuery(query);
			
			foundCity = cityresult.getString("City");
			
			statement.close();
		}
		catch(Exception e) {
			e.getStackTrace();
		}
		
		return foundCity;
	}







}

